import { Inject, Provide } from '@midwayjs/core';
import { Context } from '@midwayjs/koa';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { In, Repository } from 'typeorm';
import {
  CreateTeamDto,
  UpdateTeamDto,
  QueryTeamDto,
  teamTeacherDto,
} from '@dto/team.dto';
import { TeamEntity } from '@entity/team.entity';
import { resBuild } from '../utils/resBuild';
import { getOperator, isEmpty } from '../utils';
import { GroupEntity } from '@entity/group.entity';
import { StudentTeamEntity } from '@entity/studentTeam.entity';
import { StudentEntity } from '@entity/user/student.entity';
import { User } from '@entity/framework/system/SysUser';
import { TeamTeacherEntity } from '@entity/teamTeacher.entity';
@Provide()
export class TeamDao {
  @Inject()
  ctx: Context;
  @InjectEntityModel(TeamEntity)
  teamEntityRep: Repository<TeamEntity>;

  @InjectEntityModel(GroupEntity)
  groupEntityRep: Repository<GroupEntity>;

  @InjectEntityModel(StudentTeamEntity)
  studentTeamEntityRep: Repository<StudentTeamEntity>;

  @InjectEntityModel(TeamTeacherEntity)
  teamTeacherEntityRep: Repository<TeamTeacherEntity>;

  async create(createTeamDto: CreateTeamDto) {
    const operator = getOperator(this.ctx);
    const group = await this.groupEntityRep.findOne({
      where: {
        groupId: createTeamDto.groupId,
      },
    });
    if (new Date(group.endTime) < new Date()) {
      throw new Error('该毕设分组已经结束，不能创建队伍');
    }
    return await this.teamEntityRep.manager.transaction(async manager => {
      const teamRepo = manager.getRepository(TeamEntity);
      const studentTeamRepo = manager.getRepository(StudentTeamEntity);
      const teamTeacherRepo = manager.getRepository(TeamTeacherEntity);

      // 1. 创建队伍基本信息
      const entity = teamRepo.create({
        groupId: createTeamDto.groupId,
        teamName: createTeamDto.teamName,
        teamLink: createTeamDto.teamLink,
        summary: createTeamDto.summary,
        selectFlag: createTeamDto.selectFlag || '0',
        remark: createTeamDto.remark,
        status: createTeamDto.status || '0',
        delFlag: '0',
      });
      entity.setCreateBy(operator);
      entity.setUpdateBy(operator);
      const savedTeam = await teamRepo.save(entity);
      const teamId = savedTeam.teamId;

      // 2. 创建学生-队伍关联
      if (Array.isArray(createTeamDto.students) && createTeamDto.students.length > 0) {
        const studentTeamEntities = createTeamDto.students
          .filter(s => s && s.studentId !== undefined && s.studentId !== null)
          .map(s => {
            const isLeader = s.isLeader === '1' ? '1' : '0';
            return studentTeamRepo.create({
              teamId,
              studentId: Number(s.studentId),
              isLeader,
            });
          });

        if (studentTeamEntities.length > 0) {
          // 检查学生是否已加入其他队伍
          const studentIds = studentTeamEntities.map(st => st.studentId);
          const existingStudentTeams = await studentTeamRepo.find({
            where: { studentId: In(studentIds) },
          });

          if (existingStudentTeams.length > 0) {
            const existingStudentIds = existingStudentTeams.map(st => st.studentId);
            throw new Error(`学生ID为 ${existingStudentIds.join(', ')} 的学生已加入其他队伍，无法重复加入。`);
          }

          await studentTeamRepo.save(studentTeamEntities);
        }
      }

      // 3. 创建教师志愿者关联（按排序）
      if (createTeamDto.teacherVolunteers && createTeamDto.teacherVolunteers.trim()) {
        const teacherIds = createTeamDto.teacherVolunteers
          .split(',')
          .map(id => id.trim())
          .filter(id => id && !isNaN(Number(id)))
          .map(id => Number(id));

        if (teacherIds.length > 0) {
          const teamTeacherEntities = teacherIds.map((teacherId, index) => {
            return teamTeacherRepo.create({
              teamId,
              teacherId,
              sort: index + 1, // 志愿排序：1, 2, 3...
              selectFlag: 0,
            });
          });
          await teamTeacherRepo.save(teamTeacherEntities);
        }
      }

      return resBuild.success();
    });
  }

  async list(query: QueryTeamDto) {
    const entity = this.teamEntityRep
      .createQueryBuilder('entity')
      .leftJoinAndSelect('entity.group', 'group')
      .leftJoinAndSelect('entity.studentTeams', 'studentTeams')
      .leftJoinAndSelect('entity.teamTeachers', 'teamTeachers')
      .leftJoinAndSelect('teamTeachers.teacher', 'teacher', 'teacher.delFlag = :teacherDelFlag', { teacherDelFlag: '0' })
      .leftJoinAndSelect('studentTeams.student', 'students')
      .leftJoinAndSelect('students.user', 'studentUser')
      .leftJoinAndSelect('students.major', 'studentMajor')
      .leftJoinAndSelect('teacher.user', 'teacherUser')
      .where('entity.delFlag = :delFlag', { delFlag: '0' });

    // 原有的精确查询
    if (!isEmpty(query.groupId)) {
      entity.andWhere('entity.groupId = :groupId', { groupId: query.groupId });
    }
    if (!isEmpty(query.teamId)) {
      entity.andWhere('entity.teamId = :teamId', { teamId: query.teamId });
    }
    if (!isEmpty(query.selectFlag)) {
      entity.andWhere('entity.selectFlag = :selectFlag', {
        selectFlag: query.selectFlag,
      });
    }
    if (!isEmpty(query.status)) {
      entity.andWhere('entity.status = :status', { status: query.status });
    }

    // 新增模糊搜索字段
    if (!isEmpty(query.groupName)) {
      entity.andWhere('group.groupName LIKE :groupName', {
        groupName: `%${query.groupName}%`
      });
    }

    if (!isEmpty(query.teamName)) {
      entity.andWhere('entity.teamName LIKE :teamName', {
        teamName: `%${query.teamName}%`
      });
    }

    if (!isEmpty(query.teamLink)) {
      entity.andWhere('entity.teamLink LIKE :teamLink', {
        teamLink: `%${query.teamLink}%`
      });
    }

    if (!isEmpty(query.summary)) {
      entity.andWhere('entity.summary LIKE :summary', {
        summary: `%${query.summary}%`
      });
    }

    // isAssigned: 0-未分配，1-已分配（基于teamTeachers的selectFlag判断）
    if (!isEmpty(query.isAssigned)) {
      if (query.isAssigned == '0') {
        // 未分配：没有教师关联或者所有教师都未选中
        entity.andWhere('entity.selectFlag = :selectFlag', {
          selectFlag: '0',
        });
      } else if (query.isAssigned == '1') {
        // 已分配：至少有一个教师被选中
        entity.andWhere('entity.selectFlag = :selectFlag', {
          selectFlag: '1',
        });
      }
    }

    // 学生学号模糊搜索（使用子查询找出包含该学生的队伍）
    if (!isEmpty(query.stuId)) {
      const subQuery = this.studentTeamEntityRep
        .createQueryBuilder('st')
        .leftJoin('st.student', 's')
        .select('st.teamId')
        .where('s.stuId LIKE :stuId', { stuId: `%${query.stuId}%` })
        .getQuery();

      entity.andWhere(`entity.teamId IN (${subQuery})`, { stuId: `%${query.stuId}%` });
    }

    // 学生姓名模糊搜索（使用子查询找出包含该学生的队伍）
    if (!isEmpty(query.studentName)) {
      const subQuery = this.studentTeamEntityRep
        .createQueryBuilder('st')
        .leftJoin('st.student', 's')
        .leftJoin('s.user', 'u')
        .select('st.teamId')
        .where('u.nickName LIKE :studentName', { studentName: `%${query.studentName}%` })
        .getQuery();

      entity.andWhere(`entity.teamId IN (${subQuery})`, { studentName: `%${query.studentName}%` });
    }

    // 教师姓名模糊搜索（使用子查询找出包含该教师的队伍）
    if (!isEmpty(query.teacherName)) {
      const subQuery = this.teamTeacherEntityRep
        .createQueryBuilder('tt')
        .leftJoin('tt.teacher', 't')
        .leftJoin('t.user', 'u')
        .select('tt.teamId')
        .where('u.nickName LIKE :teacherName', { teacherName: `%${query.teacherName}%` })
        .andWhere('t.delFlag = :delFlag', { delFlag: '0' })
        .getQuery();

      entity.andWhere(`entity.teamId IN (${subQuery})`, { teacherName: `%${query.teacherName}%` });
    }
    // 教师ID精确查询
    if (!isEmpty(query.teacherId) && query.isSelected === '1') {
      // 已选择：队伍中包含该教师
      entity.andWhere('teacher.teacherId = :teacherId', { teacherId: query.teacherId });
    }
    if (!isEmpty(query.teacherId) && query.isSelected === '0') {
      // 未选择：队伍中不包含该教师（使用子查询排除）
      const subQuery = this.teamTeacherEntityRep
        .createQueryBuilder('tt')
        .select('tt.teamId')
        .where('tt.teacherId = :teacherId', { teacherId: query.teacherId })
        .getQuery();

      entity.andWhere(`entity.teamId NOT IN (${subQuery})`, { teacherId: query.teacherId });
    }



    if (query.pageNum && query.pageSize) {
      entity.skip(query.pageSize * (query.pageNum - 1)).take(query.pageSize);
    }

    entity.addOrderBy('teamTeachers.selectFlag', 'DESC');
    entity.addOrderBy('entity.createTime', 'DESC');

    const [rows, total] = await entity.getManyAndCount();
    return resBuild.list(rows, total);
  }

  async detail(teamId: number) {
    const qb = this.teamEntityRep
      .createQueryBuilder('entity')
      .leftJoinAndSelect('entity.group', 'group')
      .leftJoinAndSelect('entity.studentTeams', 'studentTeams')
      .leftJoinAndSelect('entity.teamTeachers', 'teamTeachers')
      .leftJoinAndSelect('teamTeachers.teacher', 'teacher', 'teacher.delFlag = :teacherDelFlag', { teacherDelFlag: '0' })
      .leftJoinAndSelect('studentTeams.student', 'students')
      .leftJoinAndSelect('students.user', 'studentUser')
      .leftJoinAndSelect('teacher.user', 'teacherUser')
      .where('entity.teamId = :teamId', { teamId })
      .andWhere('entity.delFlag = :delFlag', { delFlag: '0' });
    const row = await qb.getOne();
    return resBuild.data(row);
  }

  async update(updateTeamDto: UpdateTeamDto) {
    const group = await this.groupEntityRep.findOne({
      where: {
        groupId: updateTeamDto.groupId,
      },
    });
    if (new Date(group.endTime) < new Date()) {
      throw new Error('该毕设分组已经结束，不能更新队伍');
    }
    // 极简成员更新：支持 students 数组和 teacherVolunteers 字符串
    const operator = getOperator(this.ctx);
    await this.teamEntityRep.manager.transaction(async manager => {
      const teamRepo = manager.getRepository(TeamEntity);
      const stRepo = manager.getRepository(StudentTeamEntity);
      const teamTeacherRepo = manager.getRepository(TeamTeacherEntity);

      // 1) 更新队伍基本信息
      // 排除 students 和 teacherVolunteers 字段（这些是业务逻辑字段，不是实体字段）
      const { students, teacherVolunteers, ...teamData } = updateTeamDto;
      const entity = teamRepo.create(teamData);
      entity.setUpdateBy(operator);
      await teamRepo.save(entity);

      // 2) 同步成员
      if (Array.isArray(updateTeamDto.students)) {
        const teamId = updateTeamDto.teamId;
        await stRepo
          .createQueryBuilder()
          .delete()
          .from(StudentTeamEntity)
          .where('team_id = :teamId', { teamId })
          .execute();

        const values = updateTeamDto.students
          .filter(s => s && s.studentId !== undefined && s.studentId !== null)
          .map(s => ({
            studentId: Number(s.studentId),
            teamId: Number(teamId),
            isLeader:
              s.isLeader === '1'
                ? '1'
                : '0',
          }));
        if (values.length > 0) {
          await stRepo
            .createQueryBuilder()
            .insert()
            .into(StudentTeamEntity)
            .values(values)
            .execute();
        }
      }

      // 3) 同步教师志愿者
      if (updateTeamDto.teacherVolunteers !== undefined) {
        const teamId = updateTeamDto.teamId;
        // 先删除旧的教师关联
        await teamTeacherRepo
          .createQueryBuilder()
          .delete()
          .from(TeamTeacherEntity)
          .where('team_id = :teamId', { teamId })
          .execute();

        // 处理新的教师志愿者
        if (updateTeamDto.teacherVolunteers && updateTeamDto.teacherVolunteers.trim()) {
          const teacherIds = updateTeamDto.teacherVolunteers
            .split(',')
            .map(id => id.trim())
            .filter(id => id && !isNaN(Number(id)))
            .map(id => Number(id));

          if (teacherIds.length > 0) {
            const teamTeacherEntities = teacherIds.map((teacherId, index) =>
              teamTeacherRepo.create({
                teamId: Number(teamId),
                teacherId,
                sort: index + 1, // 志愿排序：1, 2, 3...
                selectFlag: 0,
              })
            );
            await teamTeacherRepo.save(teamTeacherEntities);
          }
        }
      }
    });
    return resBuild.success();
  }

  async remove(teamIds: Array<number>) {
    return await this.teamEntityRep.manager.transaction(async manager => {
      const teamRepo = manager.getRepository(TeamEntity);
      await teamRepo.delete({ teamId: In(teamIds) });
      return resBuild.success();
    });
  }
  async createTeamTeacher(teamTeacherDto: teamTeacherDto) {
    const { teamId, teachers } = teamTeacherDto;
    await this.teamTeacherEntityRep.manager.transaction(async manager => {
      const teamRepo = manager.getRepository(TeamEntity);
      const teamTeacherRepo = manager.getRepository(TeamTeacherEntity);

      const existTeam = await teamRepo.findOneBy({ teamId });
      if (!existTeam) {
        throw new Error('团队记录不存在');
      }

      // 数据库没有 del_flag，使用物理删除清空旧志愿记录
      await teamTeacherRepo.delete({ teamId });

      const list = (teachers || []).map(t =>
        teamTeacherRepo.create({
          teamId,
          teacherId: t.teacherId,
          sort: t.sort,
        } as Partial<TeamTeacherEntity>)
      );
      if (list.length > 0) {
        await teamTeacherRepo.save(list);
      }
    });

    return resBuild.success();
  }

  async getCrrentTeam(teacherId: number) {
    const qb = this.teamTeacherEntityRep
      .createQueryBuilder('tt')
      .leftJoin(TeamEntity, 'team', 'team.teamId = tt.teamId')
      .leftJoin(StudentTeamEntity, 'studentTeam', 'tt.teamId = studentTeam.teamId')
      .leftJoin(StudentEntity, 'stu', 'studentTeam.student_id = stu.student_id')
      .leftJoin(User, 'usr', 'stu.user_id = usr.user_id')
      .where('tt.teacherId = :teacherId', { teacherId })
      .andWhere('team.del_flag = :delFlag', { delFlag: '0' });

    qb.select([
      'tt.teamId AS "teamId"',
      'tt.teacherId AS "teacherId"',
      'tt.sort AS "sort"',
      'tt.selectFlag AS "selectFlag"',
      'team.teamName AS "teamName"',
      'team.groupId AS "groupId"',
      'team.teamLink AS "teamLink"',
      'team.summary AS "summary"',
      'team.selectFlag AS "teamSelectFlag"',
      'team.status AS "teamStatus"',
    ]);
    qb.addSelect(
      "COALESCE(json_agg(json_build_object('studentId', studentTeam.student_id, 'isLeader', studentTeam.is_leader, 'stuId', stu.stu_id, 'email', stu.email, 'summary', stu.summary, 'gradeId', stu.grade_id, 'majorId', stu.major_id, 'remark', stu.remark, 'status', stu.status, 'nickName', usr.nick_name)) FILTER (WHERE studentTeam.student_id IS NOT NULL), '[]'::json)",
      'students'
    );

    qb.groupBy(
      [
        'tt.teamId',
        'tt.teacherId',
        'tt.sort',
        'tt.selectFlag',
        'team.teamName',
        'team.groupId',
        'team.teamLink',
        'team.summary',
        'team.selectFlag',
        'team.status',
      ].join(', ')
    );

    const rows = await qb.getRawMany();
    return resBuild.data(rows);
  }
}
